# @Project: diablo
# @File: agreement_hongde_sub
# @Time: 2023/12/19 15:51
# @User: Jolly
# @Email: jolly@007vin.com

"""
宏德分公司直接定价，定价来源于总仓和一线
总仓（总仓价格模板）：定价所有其他子公司（一线除外）
一线（一线价格模板）：总仓价格固定定价（全复制），定价所有其他子公司
其他子公司（自己价格模板）：总仓和一线全复制，定价自己和其他兄弟公司
"""

import datetime
from datetime import timedelta
import time
import threading
import requests
from queue import Queue
from copy import deepcopy

from io import BytesIO
from openpyxl import load_workbook
from pymongo import MongoClient, InsertOne, UpdateOne
from pymongo.errors import ConnectionFailure
import pymysql

from dbutils.pooled_db import PooledDB

lock = threading.Lock()


class InitAgreementPrice:

    def __init__(self):

        self.recent_update_goods_data = {}

        self.hongde_main = ["3"]  # 总仓

        self.hongde_yixian = ["1390f84351fe464b90dcdbf3a164ff24"]  # 一线

        self.main_group = ["3", "1390f84351fe464b90dcdbf3a164ff24"]  # 总仓和一线

        # 宏德分公司（除了总仓和一线）
        self.hongde_group = ["fc2e72e26a03423c8f8ee8576debf2ec",  # 龙华鑫众奥
                             "3fd50208a10c42ac87c6d84bd9f5661f",  # 龙华名捷
                             "a62152e4fe1f49d186d23e060f272e65",  # 龙华名诚行
                             "1b5044001e05415ab9f19afbeb73b9c7",  # 罗湖德众
                             "3d80a9253b8640d4afc1da174a39d085",  # 罗湖名捷
                             "230afea849fd489d88aa6dfeab26a3b3",  # 罗湖名诚行
                             "8f5992a1d102410e95cd76f70f0cec30",  # 龙岗德众
                             "c217b4952279478889397f15d9968735",  # 龙岗名捷
                             "84c2ec02335b4f7f8cabcbbda0d133af",  # 龙岗名诚行
                             "411e70375f484ae0b7948b326401a205",  # 平湖鑫德众
                             "b8b351a60d114d3a8a078ad178eb41c5",  # 平湖名捷
                             "f73a74ddc6584457bd573b400e217110",  # 平湖名诚行
                             "0e402537190b4677b5d6d8a1c8af0b2f",  # 寮步鑫众奥
                             "c7823ee69b7b4ed4ae8ddab3ee0c12c7",  # 寮步路捷
                             "92560c123ce347b9806b6756a0d54097",  # 寮步名诚行
                             "79f7412227fb4928bb12f2dc11048a11",  # 沙井鑫众奥
                             "4b2c95dd10654036909423de19777d5d",  # 沙井名捷
                             "920980642c63490abfad211727bbee7d",  # 沙井名诚行
                             "e44f3831dede4640b9bdd40eeaabdc26",  # 宝安鑫众奥
                             "50ff6b30688d40b48879857847dcc320",  # 宝安名捷
                             "192b5c4f311442cbb613ed63a07d8f78",  # 宝安名诚行
                             "c982247f26b74166884d0ca2087d5c17",  # 厚街鑫众奥
                             "acf15daf375f497390ac274538e3e058",  # 厚街名捷
                             "a2f1c6009ca44cbf8b8e6ddb685b5eae",  # 厚街名诚行
                             "3546da2f191b4d1cb8979dad8e42593f",  # 塘厦鑫众奥
                             "203ffd0f7d3645f4ba59387b6b532308",  # 塘厦名捷
                             "cd5ce52f2db34e39bc66e88bc41238b0",  # 塘厦名诚行
                             "063fcf25de0647d29ed12cbb2832efa9",  # 惠阳鑫德众,
                             "096ba895768349b0ae28bd4d9f034628",  # 惠阳名诚行,
                             "0c222f3399fe4aa494bc79e6fe363a82",  # 惠阳名捷,
                             ]

        start_time = time.time()

        # 初始化数据库连接
        # 2.0生产库
        self.diablo_conn_2_0 = self.get_mongo_connection("root", "aa123456", "192.168.191.175", "27017")

        # 初始化数据库连接
        self.mysql_conn = self.get_mysql_connection()

        self.get_recent_update_goods_data()

        # 执行任务
        self.task()

        print("========= 数据插入,共耗时:{}'s =========".format(round(time.time() - start_time, 3)))

    def get_mongo_connection(self, username, password, host, port):
        try:
            # 创建 MongoClient
            client = MongoClient(f"mongodb://{username}:{password}@{host}:{port}",
                                 maxPoolSize=300,
                                 minPoolSize=1,
                                 maxIdleTimeMS=30000,
                                 waitQueueTimeoutMS=2000)

            # 检测连接是否可用，如果连接不可用则抛出异常
            if not client:
                raise ConnectionFailure("从MongoDB连接池中获取连接失败！")

            return client

        except ConnectionFailure as e:
            # 处理连接失败的异常
            print(f"连接MongoDB失败: {e}")
            return None

    def get_mysql_connection(self):
        maxconnections = 200  # 最大连接数
        pool = PooledDB(
            pymysql,
            maxconnections,
            host='192.168.191.21',
            user='users',
            port=3306,
            passwd='uSR@ll7GnJDopei',
            db='users',
            use_unicode=True)

        # pool = PooledDB(
        #     pymysql,
        #     maxconnections,
        #     host='192.168.191.47',
        #     user='users',
        #     port=3306,
        #     passwd='uSR@#H^&JD',
        #     db='users',
        #     use_unicode=True)

        return pool

    # 判断定价协议表是否已有对应数据，有的话就删除
    def check_agreement_exist(self, code, agreement_excel_id, merchant_id):

        check = False

        connection = self.diablo_conn_2_0

        try:
            if connection is not None:
                # 执行数据库操作
                db = connection["inventory_data"]
                conn = db["agreement_goods_price"]

                filter_dict = {
                    "code": code,
                    "merchant_id": merchant_id,
                    "agreement_excel_id": agreement_excel_id
                }
                docs_count = conn.count_documents(filter_dict)

                if docs_count > 0:
                    # 删除已有的
                    # conn.delete_many(filter_dict)
                    check = True
                    return True, filter_dict  # 准备做更新操作

        except Exception as e:
            print("获取协议定价存在检测失败 - mongo：", e)
        finally:
            # 将连接放回连接池
            connection.close()

        return check, {}

    # 查找总仓、一线定价记录
    def get_agreement_price(self, code, current_merchant_id):

        check = False

        # connection = self.diablo_conn_2_0
        agreement_price = 0

        # 当前如果是一线，则直接查找宏德模板的一线的定价
        if current_merchant_id == "1390f84351fe464b90dcdbf3a164ff24":

            yixian_data = self.recent_update_goods_data.get("1390f84351fe464b90dcdbf3a164ff24", {})
            agreement_price = yixian_data.get(code, 0)
            if agreement_price != 0:
                check = True
                return check, agreement_price
            # seed_group = deepcopy(self.hongde_yixian)
        else:
            # seed_group = deepcopy(self.main_group)
            agreement_price = 0
            for _, goods_data in self.recent_update_goods_data.items():
                agreement_price = goods_data.get(code, 0)
                if agreement_price == 0:
                    continue
                else:
                    check = True
                    return check, agreement_price

        #
        # try:
        #     if connection is not None:
        #         # 执行数据库操作
        #         db = connection["inventory_data"]
        #         conn = db["agreement_goods_price"]
        #
        #         # 获取总仓、一线定价记录
        #         for merchant_id in seed_group:
        #
        #             filter_dict = {
        #                 "code": code,
        #                 "merchant_id": merchant_id,
        #                 "agreement_excel_id": agreement_excel_id,
        #                 "price_time": {"$gt": one_hour_ago_formatted},  # 一小时内的定价
        #             }
        #
        #             docs_count = conn.count_documents(filter_dict)
        #
        #             if docs_count == 0:
        #                 continue
        #             else:
        #                 docs = conn.find(filter_dict, {'agreement_price': 1})
        #
        #                 for doc in docs:
        #                     agreement_price = doc.get("agreement_price", 0)
        #                     if agreement_price != 0:
        #                         check = True
        #                         return check, agreement_price
        #
        # except Exception as e:
        #     print("获取总仓、一线定价失败 - mongo：", e)
        # finally:
        #     # 将连接放回连接池
        #     connection.close()

        return check, agreement_price

    def get_merchant_agreement_excel(self, merchant_ids):
        """
        获取商家价格模板列表
        """

        agreement_info_dict = {}

        mysql_conn = self.mysql_conn.connection()

        try:
            cursor = mysql_conn.cursor()

            hongde_group_str = ''
            for index, data in enumerate(merchant_ids):

                if index == len(merchant_ids) - 1:
                    hongde_group_str = hongde_group_str + ", '" + str(data) + ""
                    break
                else:
                    hongde_group_str = hongde_group_str + ", '" + str(data) + "'"

            hongde_group_str = hongde_group_str[3:]

            sql = f"select merchant_id, id from diablo_excel_agreement where merchant_id in ('{hongde_group_str}') and status=1"

            cursor.execute(sql)

            result = cursor.fetchall()

            # {"3": [], "4": []}

            if len(result) > 0:

                for result_ in result:

                    agreement_list = agreement_info_dict.get(result_[0])

                    if agreement_list:
                        exist_list = deepcopy(agreement_list)
                        exist_list.append(result_[1])
                        agreement_info_dict[result_[0]] = exist_list
                    else:
                        agreement_info_dict[result_[0]] = [result_[1]]

        except Exception as e:
            print("获取价格模板信息失败 - mysql：", e)
        finally:
            mysql_conn.close()

        return agreement_info_dict

    def bulk_operate_agreement_excel(self, bulk_insert_list, bulk_update_list):
        """批量操作 写入、更新等"""

        operate_list = []

        if bulk_insert_list:
            for data in bulk_insert_list:
                operate_list.append(InsertOne(data))

        if bulk_update_list:
            for data in bulk_update_list:
                operate_list.append(UpdateOne(data.get("filter_dict"), data.get("update_dict")))

        # 执行批量更新
        # 获取2.0MongoDB连接
        connection = self.diablo_conn_2_0

        try:
            if connection is not None:
                # 执行数据库操作
                db = connection["inventory_data"]
                conn = db["agreement_goods_price"]
                conn.bulk_write(operate_list)

        except Exception as e:
            print("写入2.0MongoDB数据库单据失败：", e)
        finally:
            # 将连接放回连接池
            connection.close()

    # 写入数据
    def write_data(self, *args):

        goods = args[0]
        stock_related = args[1]
        agreement_info_dict = args[2]

        agreement_info_dict_temp = deepcopy(agreement_info_dict)

        code = goods.get("code")

        # is_online = goods.get("is_online")
        # if not is_online:
        #     print(f"商品无库存，已下线")
        #     return

        # 如果当前是宏德总仓，则去除宏德价格模板，不用写入
        if stock_related.get('merchant_id') in ("3", "1390f84351fe464b90dcdbf3a164ff24"):
            del agreement_info_dict_temp['3']

        # 查找总仓、一线定价记录
        check, agreement_price = self.get_agreement_price(code, stock_related.get('merchant_id'))

        # 没有定价
        if not check:
            return

        # print(1, code, agreement_price)

        bulk_insert_list = []
        bulk_update_list = []

        for _, agreement_ids in agreement_info_dict_temp.items():

            for agreement_excel_id in agreement_ids:

                # 检查新表是否存在库存
                check, filter_dict_exist = self.check_agreement_exist(code, agreement_excel_id, stock_related.get("merchant_id"))
                if not check:

                    agreement_dict = {
                        "agreement_excel_id": agreement_excel_id,
                        "code": code,
                        "merchant_id": stock_related.get("merchant_id"),
                        "agreement_price": agreement_price,
                        "price_type": "customize",
                        "price_value": agreement_price,
                        "price_desc": f"¥ = {str(agreement_price)}",
                        "update_time": datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
                        "user_id": "0000000000000000003",  # 当前用户id
                        "price_user": "系统人工控价",  # 当前用户名
                        "price_time": datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
                    }

                    bulk_insert_list.append(dict(agreement_dict))

                else:
                    # 更新操作
                    # filter_dict_exist = {
                    #     "code": code,
                    #     "merchant_id": merchant_id,
                    #     "agreement_excel_id": agreement_excel_id
                    # }

                    # print(2, code, agreement_price)

                    update_dict = {"$set": {
                        "agreement_excel_id": agreement_excel_id,
                        "code": code,
                        "merchant_id": stock_related.get("merchant_id"),
                        "agreement_price": agreement_price,
                        "price_type": "customize",
                        "price_value": agreement_price,
                        "price_desc": f"¥ = {str(agreement_price)}",
                        "update_time": datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
                        "user_id": "0000000000000000003",  # 当前用户id
                        "price_user": "系统人工控价",  # 当前用户名
                        "price_time": datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
                    }}

                    # 拼入批量列表中
                    update_items = {"filter_dict": filter_dict_exist, "update_dict": update_dict}
                    bulk_update_list.append(update_items)

                    # # 获取2.0MongoDB连接
                    # connection = self.diablo_conn_2_0
                    #
                    # try:
                    #     if connection is not None:
                    #         # 执行数据库操作
                    #         db = connection["inventory_data"]
                    #         conn = db["agreement_goods_price"]
                    #         conn.insert_one(dict(agreement_dict))
                    #
                    # except Exception as e:
                    #     print("写入2.0MongoDB数据库单据失败：", e)
                    # finally:
                    #     # 将连接放回连接池
                    #     connection.close()

        if len(bulk_insert_list) > 0 or len(bulk_update_list) > 0:
            try:
                self.bulk_operate_agreement_excel(bulk_insert_list, bulk_update_list)
            except Exception as e:
                print("批量写入定价记录失败：", e)

    def get_stock_related(self, hongde_merchant_ids):
        """
        查找库存关联表查找有效的库存关系
        """
        stock_related_list = []

        mysql_conn = self.mysql_conn.connection()

        try:
            cursor = mysql_conn.cursor()

            # hongde_merchant_ids = ["3", "1390f84351fe464b90dcdbf3a164ff24"]
            #
            # hongde_merchant_ids.extend(self.hongde_group)

            hongde_group_str = ''
            for index, data in enumerate(hongde_merchant_ids):

                if index == len(hongde_merchant_ids) - 1:
                    hongde_group_str = hongde_group_str + ", '" + str(data) + ""
                    break
                else:
                    hongde_group_str = hongde_group_str + ", '" + str(data) + "'"

            hongde_group_str = hongde_group_str[3:]

            sql = f"""select merchant_id, merchant_name, merchant_phone, merchant_company_id, company_name, erp_type,
                         erp_uid, app_key, goods_cst_id from diablo_stock_related where status=1 and merchant_id in ('{hongde_group_str}') """

            cursor.execute(sql)

            result = cursor.fetchall()

            if len(result) > 0:

                for result_ in result:
                    stock_related_list.append({
                        "merchant_id": result_[0],
                        "merchant_name": result_[1],
                        "merchant_phone": result_[2],
                        "merchant_company_id": result_[3],
                        "company_name": result_[4],
                        "erp_type": result_[5],
                        "erp_uid": result_[6],
                        "app_key": result_[7],
                        "goods_cst_id": result_[8]
                    })

        except Exception as e:
            print("获取库存关联信息失败 - mysql：", e)
        finally:
            mysql_conn.close()

        return stock_related_list

    def get_cst_goods(self, cst_merchant_id):
        """
        根据厂商通id查找所有商品数据
        """

        # 测试环境
        # url = "http://192.168.191.131:8900/v1/api/openapi/goods/xqp/alllist"
        # 正式环境
        url = "http://192.168.191.245:9960/v1/api/openapi/goods/xqp/alllist"

        headers = {
            "Content-Type": "application/json"
        }

        params = {
            'yc_id': 'xqp0000000000000000001',
            'merchant_id': cst_merchant_id
        }
        response = requests.get(url=url, headers=headers, params=params)
        resp = response.json()

        if resp.get('code') != 1:
            print("厂商通id查找所有商品数据失败")

        return resp['data']

    def get_recent_update_goods_data(self):
        """
        获取总仓和一线最近更新的商品集合
        """

        # 获取一小时前的时间
        current_time = datetime.datetime.now()
        one_hour_ago = current_time - timedelta(hours=1)

        # 格式化时间为指定格式
        one_hour_ago_formatted = one_hour_ago.strftime("%Y-%m-%d %H:%M:%S")

        merchant_goods_data = {}

        for merchant_id in self.main_group:

            # 获取所有宏德库存记录
            stock_related_list = self.get_stock_related([merchant_id])

            goods_cst_id = stock_related_list[0].get("goods_cst_id")

            # 获取库存商品列表
            goods_list = self.get_cst_goods(goods_cst_id)

            # 遍历商品，查询近一小时更新的产品，并组装返回
            for goods in goods_list:

                code = goods.get("code")

                # is_online = goods.get("is_online")
                # if not is_online:
                #     print(f"商品无库存，已下线")
                #     return

                filter_dict = {
                    "code": code,
                    "merchant_id": merchant_id,
                    "agreement_excel_id": 33,
                    "price_time": {"$gt": one_hour_ago_formatted},  # 一小时内的定价
                }

                connection = self.diablo_conn_2_0
                if connection is not None:
                    # 执行数据库操作
                    db = connection["inventory_data"]
                    conn = db["agreement_goods_price"]

                    docs_count = conn.count_documents(filter_dict)

                    if docs_count == 0:
                        continue
                    else:
                        docs = conn.find(filter_dict, {'agreement_price': 1})

                        for doc in docs:
                            agreement_price = doc.get("agreement_price", 0)
                            if agreement_price != 0:
                                goods_data = merchant_goods_data.get(merchant_id)
                                if goods_data:
                                    temp_data = deepcopy(goods_data)
                                    temp_data[code] = agreement_price
                                    merchant_goods_data[merchant_id] = temp_data
                                else:
                                    merchant_goods_data[merchant_id] = {code: agreement_price}

                                break
        self.recent_update_goods_data = merchant_goods_data
        # print(merchant_goods_data)
        # return merchant_goods_data

    # 任务执行
    def task(self):
        q = Queue(maxsize=250)  # 设定最大队列数和线程数

        count = 1

        # 获取总仓和一线，近一个小时更新的商品列表
        # self.get_recent_update_goods_data()

        hongde_merchant_ids = ["3", "1390f84351fe464b90dcdbf3a164ff24"]
        hongde_merchant_ids.extend(self.hongde_group)

        # 获取所有宏德库存记录
        stock_related_list = self.get_stock_related(hongde_merchant_ids)

        for stock_related in stock_related_list:

            goods_cst_id = stock_related.get("goods_cst_id")

            # 获取库存商品列表
            goods_list = self.get_cst_goods(goods_cst_id)

            # 获取所有价格模板
            agreement_excel_merchant_ids = []
            agreement_excel_merchant_ids.extend(self.main_group)
            agreement_excel_merchant_ids.extend(self.hongde_group)
            agreement_info_dict = self.get_merchant_agreement_excel(agreement_excel_merchant_ids)

            # {'3': [33, 67], '1390f84351fe464b90dcdbf3a164ff24': [111], 'fc2e72e26a03423c8f8ee8576debf2ec': [129],
            #  'cd5ce52f2db34e39bc66e88bc41238b0': [130], 'a62152e4fe1f49d186d23e060f272e65': [131],
            #  '3fd50208a10c42ac87c6d84bd9f5661f': [133], '1b5044001e05415ab9f19afbeb73b9c7': [134],
            #  '3d80a9253b8640d4afc1da174a39d085': [135], '230afea849fd489d88aa6dfeab26a3b3': [136],
            #  '8f5992a1d102410e95cd76f70f0cec30': [137], 'c217b4952279478889397f15d9968735': [138],
            #  '84c2ec02335b4f7f8cabcbbda0d133af': [139], '411e70375f484ae0b7948b326401a205': [140],
            #  'b8b351a60d114d3a8a078ad178eb41c5': [141], 'f73a74ddc6584457bd573b400e217110': [142],
            #  '0e402537190b4677b5d6d8a1c8af0b2f': [143], 'c7823ee69b7b4ed4ae8ddab3ee0c12c7': [144],
            #  '92560c123ce347b9806b6756a0d54097': [145], '79f7412227fb4928bb12f2dc11048a11': [146],
            #  '4b2c95dd10654036909423de19777d5d': [147], '920980642c63490abfad211727bbee7d': [148],
            #  'e44f3831dede4640b9bdd40eeaabdc26': [149], '50ff6b30688d40b48879857847dcc320': [150],
            #  '192b5c4f311442cbb613ed63a07d8f78': [151], 'c982247f26b74166884d0ca2087d5c17': [152],
            #  'acf15daf375f497390ac274538e3e058': [153], 'a2f1c6009ca44cbf8b8e6ddb685b5eae': [154],
            #  '3546da2f191b4d1cb8979dad8e42593f': [155], '203ffd0f7d3645f4ba59387b6b532308': [156]}

            for goods in goods_list:

                t = threading.Thread(target=self.write_data, args=(goods, stock_related, agreement_info_dict))
                q.put(t)
                if q.full() == True:
                    thread_list = []
                    while q.empty() == False:
                        t = q.get()
                        thread_list.append(t)
                        t.start()
                    for t in thread_list:
                        t.join()

                print("当前写入数：", count,
                      ", 商家为：", stock_related.get("merchant_name"),
                      ", 商品编码为：", goods.get("code"),
                      ", 名称为：", goods.get("part_name"))

                count += 1

            # 处理残余队列数据
            thread_list = []
            while q.empty() == False:
                t = q.get()
                thread_list.append(t)
                t.start()
            for t in thread_list:
                t.join()


if __name__ == "__main__":
    InitAgreementPrice()